Re: [HACKERS] Re: [INTERFACES] Odbc parser error - Mailing list pgsql-hackers

Byron Nikolaidis <byronn@insightdist.com> wrote:

> Yes, the NULL works for parameters of an update statement, where Access would
> specify a statement such as "update table set param = ? where x = 1".  But
> it doesn't work in a select statement.  I don't think I have much to work
> with here.  The statement comes in as something like "select * from table
> where x = ?".  I have to replace the ? with something.  On updates, 'NULL'
> works fine.
>
> I'm not sure what to do about this.  On other dbms, parameter passing is
> handled through a separate protocol to the backend, usually after a prepare
> statement, so on these its no problem to send a null, or large amounts of
> ascii/binary data, without having to worry about direct substitution into
> the sql string or hitting the upper limit of the statement string.  I think
> until Postgres has such as protocol for parameter substitution/passing, it
> will be difficult to fix this problem.

Well, before we jump high, I think this stems from a long-standing Postgres
problem - which I mentioned in the past, and I guess I'll continue to
mention, until we finally buy Oracle (and get a whole different set of
problems).

The problem is that in Postgres, NULL=NULL gives false.

As simple as that.

I am appaled to hear that this is still the situation. I thought by 6.4 (I
only have 6.2.1), the problem would probably be looked into, but I guess it
wasn't.

This NULL=NULL is FALSE problem explains why there is no problem in updates
(where the semantics of "=" is assignment, not comparison!).

The problem causes many other problems - like the inability to sort by two
fields when the first field may contain nulls. Why? Because sorting by two
fields means that when the values of the first fields in two rows are
compared and found equal, the second field is used for the comparison. But
if nulls are allowed, two rows with NULL in the first field are not
considered to have the same value! So, despite the nulls being sort of
"grouped together", their secondary sort fields will come out with an
arbitrary order!

This problem stems from Postgres's global definition that when you have

   operand1 operator operand2

and operand1 or operand2 are null, the result will always be NULL. That's
nice when you are trying to add 5 to a column, and expect all places where
there was NULL before to stay NULL, because NULL signifies "no data here".

Since the comparison operator is just an operator, the result of the
comparison is not really FALSE, but NULL. NULL, however, is interpreted
almost as a "false". To show this, here is an example of comparison:

testing=> create table test6 (val int);
CREATE
testing=> copy test6 from stdin;
Enter info followed by a newline
End with a backslash and a period on a line by itself.
>> 1
>> 2
>> \N
>> 4
>> \.
testing=> select ( val = 2 ) from test6;
?column?
--------
f
t

f
(4 rows)

By the way, the reason that I said "almost" is that the NOT boolean
operator, just like the binary operators I've discussed, returns NULL when
applied to NULL. Which means that NOT ( NULL = something ) will give you
the same result as NULL = something...

testing=> select ( val ) from test6 where NOT ( val = 2 );
val
---
  1
  4
(2 rows)

In short, something needs to be done about the semantics of the equality
operator. It should be treated as a special case - in order to maintain the
logic of logic, as well...

Suggested semantics:

Perhaps the general solution is always to treat NULLS as false in boolean
context, and have the equality operator return TRUE in the case where both
its operands are NULL.

Herouth
--
Herouth Maoz, B.Sc.                Work:      herouth@oumail.openu.ac.il
                                   Home:       herutma@telem.openu.ac.il
HOME PAGE:                            http://telem.openu.ac.il/~herutma/
Internet technical assistant              Open University, Telem Project